package mysqlCnnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * This class update tables : clients, residencesArea.
 * All update of this tables in this class.
 * @author michael
 * @author ido
 * @author matan
 * @author sharon
 *
 */
public class CustomersUpdate extends ConnectionTOmysql 
{
	
	private Connection connection;
	
	/**
	 * This is the default constructor
	 * @throws IlegalMySQLPasswordORUserNameException 
	 */
	public CustomersUpdate() throws IlegalMySQLPasswordORUserNameException
	{
		super();
		this.connection = super.getConnection();
	}
	
	/**
	 * This method return all clientsID as : long[]
	 * @return long[] 
	 */
	public long[] loadAllCustomers()
	{
		Statement stmt;
		long[] customersID = null;
		int len=1, i=0;
		Vector<Long> veCustomersID = new Vector<Long>();
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT cID FROM clients;");
			while(rs.next())
			{
				veCustomersID.add(rs.getLong(1));
			}	
			len = veCustomersID.size();
			customersID = new long[len];
			for (i=0; i < len; i++)
				customersID[i] = veCustomersID.get(i);			
			rs.close();
			return customersID;
		} catch (SQLException e)
		{
			return null;
		}
		catch (Exception exc)
		{
			return null;
		}		
	}
	
	/**
	 * This method load from data base customer who has ID : cID.
	 * @param cID the id of the customer to load
	 * @return String[] 
	 */
	public String[] loadCustomer(long cID)
	{
		Statement stmt;
		String[] productTostr=null;
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM clients WHERE cID = "+cID+";");
			productTostr = new String[6];
			if (rs.next())
			{	
				productTostr[0] = String.valueOf(rs.getLong(1));//cID
				productTostr[1] = rs.getString(2);//cName
				productTostr[2] = String.valueOf(rs.getInt(3));//cAge
				productTostr[3] = rs.getString(4);//ctelephoneNumber	
				productTostr[4] = String.valueOf(rs.getInt(5));//cAreaNumber
				productTostr[5] = String.valueOf(rs.getInt(6));//importance
				rs.close();		
				return productTostr;					
			}
			return null;			
		} catch (SQLException e)
		{
			return null;
		}
	}	
		
	
		/**
		 * This method returns a string array of Areas ---> area number : area name	 
		 * @return String[]
		 */
		public String[] loadAllAreas()
		{
			Statement stmt;
			String[] areaslist = null;
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM residencesarea;");
				Vector<String> vecAreas = new Vector<String>();
				while(rs.next())
				{
					vecAreas.add((String.valueOf(rs.getInt(1)))+":"+rs.getString(2));
				}	
				int i ,len = vecAreas.size();
				areaslist = new String[len];
				for (i=0; i < len; i++)
					areaslist[i] = vecAreas.get(i);			
				rs.close();
				return areaslist;
			} catch (SQLException e)
			{
				return null;
			}
			catch (Exception exc)
			{
				return null;
			}		
		}
		
		/**
		 * This method insert customer info into Data Base.
		 * Return True on success, False on error
		 * @param cid customer id
		 * @param name customer name
		 * @param age customer age
		 * @param area customer area
		 * @param phone customer phone
		 * @param prio customer priority 
		 * @return boolean
		 */
		public boolean addCustomer(long cid, String name, int age, int area, String phone, int prio)
		{		
			try 
			{
				PreparedStatement ps = (PreparedStatement) connection.prepareStatement("INSERT INTO clients VALUES (?,?,?,?,?,?);");
				ps.setLong(1, cid);
				ps.setString(2, name);
				ps.setInt(3, age);
				ps.setString(4, phone);
				if (area == 0)
					ps.setNull(5,area, null);
				else
					ps.setInt(5, area);
				ps.setInt(6, prio);
				ps.executeUpdate();
				
				ps = (PreparedStatement) connection.prepareStatement("INSERT INTO clientsmarketsectors VALUES (?,?);");
				ps.setLong(1, 3);
				ps.setLong(2, cid);
				ps.executeUpdate();
				
				ps.close();				
				return true;
			} catch (SQLException e)
				{
				 	return false;
				}
		}
		
		/**
		 * This method update customer info in Data Base.
		 * Return True on success, False on error
		 * @param cid customer id
		 * @param name customer name
		 * @param age customer age
		 * @param area customer area
		 * @param phone customer phone
		 * @return boolean 
		 */
		public boolean updateCustomer(long cid, String name, int age, int area, String phone)
		{	
			String statementstr = null;
			Statement stmt;
			if (area > 0)
				statementstr = String.format("UPDATE clients SET cName='%s', cAge=%d, " +
						"ctelephoneNumber='%s', cAreaNumber=%d WHERE cID=%d;", name,age,phone,area,cid);
			else
				statementstr = String.format("UPDATE clients SET cName='%s', cAge=%d, " +
						"ctelephoneNumber='%s' WHERE cID=%d;", name,age,phone,cid);
			try 
			{
				stmt = connection.createStatement();
				stmt.executeUpdate(statementstr);
				stmt.close();
				return true;
			} 
			catch (SQLException e)
			{
				return false;
			}
		}
		
		/**
		 * This method returns a string array of Areas ---> area number : area name	
		 * @return String[] 
		 */
		public String[] loadAllSection()
		{
			Statement stmt;
			String[] sectionlist = null;
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM sections;");
				Vector<String> vecSection = new Vector<String>();
				while(rs.next())
				{
					vecSection.add((String.valueOf(rs.getInt(1)))+":"+rs.getString(2));
				}	
				int i ,len = vecSection.size();
				sectionlist = new String[len];
				for (i=0; i < len; i++)
					sectionlist[i] = vecSection.get(i);			
				rs.close();
				return sectionlist;
			} catch (SQLException e)
			{
				return null;
			}
			catch (Exception exc)
			{
				return null;
			}		
		}
		
		/**
		 * This method insert customer authorization into Data Base.
		 * Return True on success, False on error
		 * @return boolean 
		 */
		public boolean addAuthorization(int sectionID, long customerID)
		{		
			try 
			{
				PreparedStatement ps = (PreparedStatement) connection.prepareStatement
				("INSERT INTO authorizations VALUES (?,?);");
				ps.setInt(1, sectionID);
				ps.setLong(2, customerID);
				ps.executeUpdate();
				ps.close();
				return true;
			} catch (SQLException e)
				{
				 	return false;
				}
		}
		
		/**
		 * This method load all customer authorizations from data base.
		 * Return String[] of all customer authorizations.
		 * @return  String[] 
		 */
		public String[] loadCustomerAuthorization(long customerID)
		{
			Statement stmt;
			String[] products = null;
			int len=1, i=0;
			Vector<String> Vecproduct=new Vector<String>();
			String sqlmsg = String.format("SELECT S.SectionID, S.SectionName FROM " +
					"authorizations AS A, sections AS S WHERE " +
					"A.AcID = %d AND A.ASectionID = S.SectionID;", customerID);
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				while(rs.next())
				{
					Vecproduct.add(String.format("Section ID: %-20s S_Name: %-20s",
							rs.getString(1),rs.getString(2) ));					
				}	
				len = Vecproduct.size();
				products = new String[len];
				for (i=0; i < len; i++)
					products[i] = Vecproduct.get(i);
				
				rs.close();
				return products;
			} catch (SQLException e)
			{
				return null;
			}	
		}
		
		/**
		 * This method insert area into Data Base.
		 * Return True on success False on error.
		 * @return boolean 
		 */
		public boolean addArea(int areaID, String areaName)
		{		
			try 
			{
				PreparedStatement ps = (PreparedStatement) connection.prepareStatement
				("INSERT INTO residencesarea VALUES (?,?);");
				ps.setInt(1, areaID);
				ps.setString(2, areaName);
				ps.executeUpdate();
				ps.close();
				return true;
			} catch (SQLException e)
				{
				 	return false;
				}
		}
	
		/**
		 * This method update area in Data Base.
		 * Return True on success False on error.
		 * @return boolean 
		 */
		public boolean updateArea(int areaID, String areaName)
		{	
			String statementstr = null;
			Statement stmt;
			statementstr = String.format("UPDATE residencesarea SET AreaName='%s'" +
					" WHERE AreaNumber=%d;", areaName,areaID);
			try 
			{
				stmt = connection.createStatement();
				stmt.executeUpdate(statementstr);
				stmt.close();
				return true;
			} 
			catch (SQLException e)
			{
				return false;
			}
		}
		
		/**
		 * This method load all customer in market sector
		 * Return Vector<Long> of all customer in sector.
		 * @return  Vector<Long>
		 */
		public long[] loadAllclientAtSector(int sector)
		{
			Statement stmt;
			int i, len;
			long[] client;
			Vector<Long> Vecsector = new Vector<Long>();
			String sqlmsg = String.format("SELECT cID FROM clientsmarketsectors WHERE " +
					"sNumber = %d ;", sector);
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				while(rs.next())
				{
					Vecsector.add(rs.getLong(1));					
				}	
				len = Vecsector.size();
				client = new long[len];
				for (i=0; i < len; i++)
					client[i] = Vecsector.get(i);
				rs.close();
				return client;
			} catch (SQLException e)
			{
				return null;
			}	
		}
		
		/**
		 * This method analyzes the data and update client importance.
		 * @return string
		 */
		public String analyzesSystem()
		{
			Statement stmt;
			PreparedStatement ps = null;
			String str = "";
			int[] arrsum;
			long[] client;
			int len, i, sum=0;
			Vector<String> Vec = new Vector<String>();
			String sqlmsg = ("SELECT cID3, COUNT(*) FROM purchases GROUP BY cID3;");
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				while(rs.next())
				{
					Vec.add(rs.getString(1)+":"+rs.getString(2)+".");	
				}	
				len = Vec.size();
				arrsum = new int[len];
				client = new long[len];
				for (i=0; i < len; i++)
				{
					str = Vec.get(i);
					client[i] = Long.parseLong(str.substring(0,str.indexOf(":")));
					arrsum[i] = Integer.parseInt(str.substring(str.indexOf(":")+1, str.indexOf(".")));
					sum = sum + arrsum[i];					 
				}
				for (i=0; i < len; i++)
				{
					if ((sum / arrsum[i]) > 10)
						arrsum[i] = 10;
					else
						arrsum[i] = sum / arrsum[i];
					if (arrsum[i] <= 0)
						arrsum[i] = 1;
					ps = (PreparedStatement) connection.prepareStatement("UPDATE clients SET importance = ? WHERE cID =?;");
					ps.setInt(1, arrsum[i]);
					ps.setLong(2, client[i]);
					ps.executeUpdate();				
				}				
				rs.close();				
				return "OK";
			} catch (SQLException e)
			{
				return "ERROR";
			}	
		}
		
}
